package daoJDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import connection.ConnectionDB;
import entityJDBC.User;

public class JDBCUserDao /*implements IUserDAO*/ {

	private ConnectionDB con;
	private Statement st;

	public JDBCUserDao() {
		con = con.getInstance();
		try {
			st = con.getConnection().createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*@Override*/
	public List<User> findAll() {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		try {
			ResultSet rs = st
					.executeQuery("select * from user as u inner join usertype as ut on u.idUserType=ut.idUserType");
			while (rs.next()) {

				User user = new User();
				user.setIdUser(rs.getInt("idUser"));
				user.setUserName(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setHomeNumber(rs.getString("homeNumber"));
				user.setCellNumber(rs.getString("cellNumber"));
				user.setEmail(rs.getString("email"));
				user.setDescription(rs.getString("description"));
				user.setUserType(rs.getString("title"));
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/*@Override*/
	public User findById(int id) {
		User user = new User();
		try {
			ResultSet rs = st
					.executeQuery("select * from user as u inner join usertype as ut on u.idUserType=ut.idUserType where idUser="
							+ id);
			rs.next();
			user.setIdUser(rs.getInt("idUser"));
			user.setUserName(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setFirstName(rs.getString("firstName"));
			user.setLastName(rs.getString("lastName"));
			user.setHomeNumber(rs.getString("homeNumber"));
			user.setCellNumber(rs.getString("cellNumber"));
			user.setEmail(rs.getString("email"));
			user.setDescription(rs.getString("description"));
			user.setUserType(rs.getString("title"));
		} catch (SQLException e) {
			System.out.println("The user with the specified id does not exist");
			System.out.println(e.getMessage());
		}
		return user;
	}

	/*@Override*/
	public void insert(User user) {
		try {
		
			PreparedStatement ps = con
					.getConnection()
					.prepareStatement(
							"INSERT INTO user (username, password, firstName, lastName, homeNumber, cellNumber, email, description, idUserType, isAlive)"
									+ "VALUES (?,?,?,?,?,?,?,?,?,?)");
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getFirstName());
			ps.setString(4, user.getLastName());
			ps.setString(5, user.getHomeNumber());
			ps.setString(6, user.getCellNumber());
			ps.setString(7, user.getEmail());
			ps.setString(8, user.getDescription());
			ps.setInt(9, getUserTypeID(user.getUserType()));
			ps.setBoolean(10, true);

			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/*@Override*/
	public void delete(User user) {
		// TODO Auto-generated method stub

		String sql = "update user set isAlive=true  where idUser="
				+ user.getIdUser();
		try {
			con.getConnection().createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public int getUserTypeID(String title) {
		ResultSet rs;
		int id = 0;
		try {
			rs = st.executeQuery("SELECT idUserType from userType where title='"
					+ title + "'");
			rs.next();
			id = rs.getInt("idUserType");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return id;
	}

	public void update(User user) {
		try {
			PreparedStatement ps = con
					.getConnection()
					.prepareStatement(
							"UPDATE user SET username=?, password=?, firstName=?, lastName=?, homeNumber=?, cellNumber=?, email=?, description=?, idUserType=?, isAlive=? "
									+ " WHERE idUser=?");
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getFirstName());
			ps.setString(4, user.getLastName());
			ps.setString(5, user.getHomeNumber());
			ps.setString(6, user.getCellNumber());
			ps.setString(7, user.getEmail());
			ps.setString(8, user.getDescription());
			ps.setInt(9, getUserTypeID(user.getUserType()));
			ps.setBoolean(10,user.isAlive());
			
			ps.setInt(11, user.getIdUser());

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
			e.printStackTrace();
		}

	}

	

}
